Relational Database Management Systems (RDBMSs) typically support the ability to have one or more query statements such as SQL (Structured Query Language) statements called from within the scope of execution of a parent query statement. With the support of such capabilities as the inclusion of SQL statements within User-Defined Functions (UDFs) and the calling of stored procedures (STPs) from within triggers, it is now possible for the execution of a parent query statement to result in the execution of one or more nested SQL statements.
A problem in database systems executing nested query statements is the detection and prevention of read/write and write/write conflicts (writing conflicts) that may occur between nested statements, and between nested statements and the parent statement itself. Such detection and prevention is necessary to ensure that the overall execution results of the parent and nested statements are consistent and deterministic.
A read/write conflict may occur when a SELECT statement and a DELETE statement are defined so that on execution they both act on the same table. An example of a write/write conflict is when on execution an INSERT statement and a DELETE statement act on the same table. When these conflicting actions occur within the processing of nested statements invoked by a single parent statement, it is advantageous to be able to detect and prevent these collisions. Without such detection, non-deterministic processing may result with potential resulting data sets with missing rows, incorrect results, or even infinite loops in processing.
In a database system that is implemented in a partitioned shared-nothing environment, the above problems are potentially compounded. In such a system, the nested query statements can be executing at any partition within the system and the conflicts between the nested statements can be different at each partition. In addition, the conflicting statements can be occurring at different nesting levels, relative to the original parent statement, when they collide at a partition.
One approach to avoiding such read/write and write/write (writing) conflicts is to first require routines that modify query data to be defined as such within the system. The definition of each routine includes information on what tables will be read and written by the routine. The query compiler can then set up the query access plan such that conflicts will not occur. A significant problem with this approach is that it may result in performance penalties for statements that do not, in fact, cause conflicts.
Another approach is to implement a runtime detection and prevention algorithm. During the time that a table is being modified, the database system ensures that the table cannot be accessed by a nested statement (in a trigger or UDF) or a runtime error will result. Because the nested statements are precluded from accessing the table during the time that it is modified, results of this approach are potentially affected by the access plan selected by the database system. Such an approach is also difficult to implement in a database system implemented in a partitioned, shared-nothing environment. In addition, the detection and prevention occurs only for the time during which a table is being accessed. However, protecting a table only while it is in use can make the behaviour of statements acting on that table non-deterministic. This form of conflict avoidance can be very inconvenient for the user as the user has no direct control over how the compiler chooses to process query statements and therefore the user cannot make the conflict checking predictable.
It is therefore desirable to provide detection and prevention of read/write and write/write conflicts in an query database system that permits access to data that will not cause a conflict and that is not subject to the access plans chosen by the database system.